import tushare as ts
from sqlalchemy import create_engine
import logging
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import constant
from ChooseMethods import choose_roe
from ChooseMethods import choose_inv_turn
from ChooseMethods import choose_should_receive
from ChooseMethods import choose_debt_to_assets
from ChooseMethods import choose_pe_ttm
from ChooseMethods import choose_goodwill
from TradeCalData import get_useful_cal_day

"""
备用行情
"""
# 日志
logging.basicConfig(level=logging.DEBUG, format=' %(asctime)s - %(levelname)s - %(message)s')
logging.debug('Start of program')
# 初始化数据库连接:
engine = create_engine(
    constant.get_db_path(),
    max_overflow=0,   # 超过连接池大小外最多创建的连接数
    pool_size=5,      # 连接池大小
    pool_timeout=30,  # 连接池中没有线程最多等待时间，否则报错
    pool_recycle=-1,  # 多久之后对连接池中的连接进行回收（重置）-1不回收
)

pro = ts.pro_api(constant.get_pro_token())


def query_stock_basic_and_finance(engine_, type_):
    """
    获取基本面信息
    :param engine_
    :param type_ 1.非金融， 2.房地产 3.银行
    """
    current_day = get_useful_cal_day(engine_)
    end_date = '20210331' #报告期
    if type_ == '1':
        conditions = ' where s.industry != "银行" and s.industry != "全国地产" and s.industry != "区域地产"'
    elif type_ == '2':
        conditions = ' where s.industry = "全国地产" or s.industry = "区域地产"'
    elif type_ == '3':
        conditions = ' where s.industry = "银行"'
    sql = 'SELECT s.* , db.total_mv ,db.circ_mv, db.pe_ttm, db.pb, finan.roe, finan.debt_to_assets, finan.accounts_receiv, finan.ar_turn, finan.inventories, finan.goodwill,  finan.total_cur_assets ,finan.total_revenue, finan.total_cogs  FROM stock s LEFT JOIN daily_basic db on db.ts_code = s.ts_code and  db.trade_date = ' \
          + current_day + ' LEFT JOIN ( SELECT DISTINCT (fi.ts_code) ts_code, fi.end_date,roe, fi.debt_to_assets, fi.ar_turn, b.accounts_receiv, b.inventories, b.goodwill, b.ph_pledge_loans, b.total_cur_assets , ic.total_revenue, total_cogs FROM stock s left join fina_indicator fi on fi.ts_code = s.ts_code left join balancesheet b on fi.ts_code = b.ts_code and b.end_date = fi.end_date left join income ic  on ic.ts_code = b.ts_code and ic.end_date = b.end_date  WHERE  fi.end_date = "'+ end_date +'" )  finan on finan.ts_code = s.ts_code '

    sql = sql + conditions
    stock_finance_list = pd.read_sql(sql, engine_)
    print(len(stock_finance_list))
    if len(stock_finance_list) > 0:
        stock_finance_list = choose_roe(stock_finance_list, '>=', 8) #选择 (roe >= 8) 的
        print('roe:%s' % len(stock_finance_list))
        stock_finance_list = choose_roe(stock_finance_list, '<=', 24) #选择 (roe <= 24) 的
        print('roe:%s' % len(stock_finance_list))
        stock_finance_list = choose_pe_ttm(stock_finance_list, '<=', 50)  # 选择 (pe_ttm <= 50) 的
        print('pe_ttm:%s' % len(stock_finance_list))

        if type_ == '1' or type_ == '2':
            stock_finance_list = choose_goodwill(stock_finance_list, '<=', 15)  # 选择 (商誉 <= 10) 的
            print('商誉:%s' % len(stock_finance_list))

        if type_ == '1':
            stock_finance_list = choose_inv_turn(stock_finance_list, '<=', 40) #选择 ( 存货率 <= 40) 的
            print('存货率:%s' % len(stock_finance_list))
            stock_finance_list = choose_debt_to_assets(stock_finance_list, '<=', 60) #选择 (资产负债率 <= 60) 的
            print('资产负债率:%s' % len(stock_finance_list))
            stock_finance_list = choose_should_receive(stock_finance_list, '>', 4)  # 选择 (应收款周转率 > 6) 的
            print('应收款周转率:%s' % len(stock_finance_list))

        if type_ == '2':
            stock_finance_list = choose_should_receive(stock_finance_list, '>', 6)  # 选择 (应收款周转率 > 10) 的
            print('应收款周转率:%s' % len(stock_finance_list))

        print(stock_finance_list[['ts_code', 'name', 'roe']])
        return stock_finance_list


def paint_table(choose_res):
    print(1111)
    # if len(choose_res) == 0 :
        # choose_res =
        # col_labels = ['代码', '名称', 'roe']
        # row_colors = ['red', 'gold', 'green']
        #
        # plt.table(cellText=choose_res, colWidths=[0.1] * 3, rowLabels=choose_res[0], colLabels=col_labels,
        #                      rowColours=row_colors, colColours=row_colors, loc='best')

def paint_stock_basic_and_finance(engine_, type_):
    """
    画圈展示pb/roe模型
    :param engine_
    :param type_ 1.非金融， 2.房地产 3.银行
    """
    df = query_stock_basic_and_finance(engine_, type_)
    if len(df) > 0:

        plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
        plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

        more_than_one_subplot_flag = False
        if more_than_one_subplot_flag:

            plt.subplot(221)  # 设置绘图区域
            plt.title('收盘价折线图', fontsize=14)
            plt.plot(df.loc[:, 'trade_date'], df.loc[:, 'close'], 'b-', linewidth=1)  # x轴,y轴,点或线 默认蓝线b-
            plt.ylabel('收盘价', fontsize=8)
            plt.xlabel('交易时间', fontsize=8)
            # 设置坐标轴刻度标记的大小
            plt.tick_params(axis='both', labelsize=8)

            plt.subplot(222)
            plt.title('pe拆线图', fontsize=14)
            '''
            plt.scatter(xlist, ylist, edgecolor='r', c='g', s=150)
            其中参数edgecolor表示边缘的颜色，此处设置为红色；参数c表示内部的颜色，此处设置为绿色；s表示点的大小；
            （注意：关于参数c还以如下设置c=(value1, value2, value3)，三个value取值范围0到1之间，
            分别表示红绿蓝三原色。plt.scatter(xlist,ylist, edgecolor='k',c=(1, 0, 0), s=150)见后面的图，注意区别）
            '''
            plt.scatter(df.loc[:, 'trade_date'], df.loc[:, 'pe_ttm'], edgecolor='r', c='r', s=0.5)  # x轴,y轴,点或线 默认蓝线b-
            plt.ylabel('pe_ttm', fontsize=8)
            plt.xlabel('交易时间', fontsize=8)
            # 设置坐标轴刻度标记的大小
            plt.tick_params(axis='both', which='major', labelsize=8)

            plt.subplot(212)
            plt.title('pb拆线图', fontsize=14)
            plt.plot(df.loc[:, 'trade_date'], df.loc[:, 'pb'], 'k', linewidth=1)  # x轴,y轴,点或线 默认蓝线b-
            plt.ylabel('pb', fontsize=8)
            plt.xlabel('交易时间', fontsize=8)
            # 设置坐标轴刻度标记的大小
            plt.tick_params(axis='both', labelsize=8)
            plt.tight_layout()  # 设置默认的间距，调整整体空白
            plt.subplots_adjust(wspace=0.5, hspace=0.5)  # 调整子图间距
        else:
            # 单图多折线
            x = df.loc[:, 'roe']  # 点的横坐标
            k1 = df.loc[:, 'pb']  # 线的纵坐标
            # plt.scatter(x, k1, edgecolor='b', c='b', s=0.5)  # x轴,y轴,点或线 默认蓝线b-
            # plt.xlabel("净资产收益率")  # 横坐标名字
            # plt.ylabel("市净率")  # 纵坐标名字
            # plt.legend(loc="best")  # 图例
            fig, ax = plt.subplots()
            ax.plot(x, k1, 'o', picker=1)
            text = ax.text(0.5, 0.5, 'event', ha='center', va='center', fontdict={'size': 20})
            def on_pick(event):
                line = event.artist
                x_data, y_data = line.get_data()
                ind = event.ind

                print('on pick line:', ind, np.array([x_data[ind], y_data[ind]]).T)
                print('tickit:', df.iloc[ind][['ts_code', 'name', 'pb', 'roe']])
                info = "Name={};button={};\n(x,y):{},{}(Dx,Dy):{:3.2f},{:3.2f}".format(
                    event.name, event.button, event.x, event.y, event.xdata, event.ydata)

                text.set_text(info)
                fig.canvas.draw_idle()

            fig.canvas.mpl_connect('pick_event', on_pick)
        plt.show()


# query_stock_basic_and_finance(engine)

paint_stock_basic_and_finance(engine, '1')